﻿ALTER function [dbo].[fc_T_LayCTPhieu]
(
	@id_DPNX int
)
returns table
as
	return(
		select				vt.VatTu,
							vt.MaKyHieu,
							dv.DVT,							                 
							t.Gia,
							p.SLKH,
							p.ThanhTien,
							n.KH_NguonVatTu as NguonHT,
							t.TangKeKhoang as ViTri, 
							t.SoTheKho + '-' + CAST(t.Code as varchar(5)) as SoTheKho_Code,							
							CASE nx.NX
								WHEN 'N' THEN hd.SoHopDong
								ELSE t.NguonGoc
							 END as NguonGoc
	from tblDPNX nx inner join tblSLNX p on nx.ID_DPNX = p.ID_DPNX
	inner join tblTon t on p.ID_MatHang = t.ID_MatHang
	inner join tblVatTu vt on t.ID_VatTu = vt.ID_VatTu
	inner join tblDVT dv on vt.ID_DVT = dv.ID_DVT
	left join tblNguonVatTu n on t.ID_NguonVatTu = n.ID_NguonVatTu
	left join tblHopDong hd on hd.ID_HopDong = t.ID_HopDong
	where p.ID_DPNX = @id_DPNX
	)
--**************    fc_T_QuanLySanPham    ***********
--	Ngày tạo: 18/3/2015
--	Người tạo: Hồng Thanh
--	Mục đích: Lấy danh sách các phiếu theo sản phẩm
--****************************************************/
CREATE function [dbo].[fc_T_QuanLySanPham]()
returns table
as
	return (
		select distinct
			CASE 
				WHEN MONTH (p.NgayPhieu)> 9 THEN CAST(p.SoPhieu as varchar(10)) + p.DuoiPhieu + '/' + CAST (MONTH(p.NgayPhieu) as varchar(2)) + '-' + RIGHT(CAST(YEAR(p.NgayPhieu) as varchar(4)), 2)
                ELSE CAST(p.SoPhieu as varchar(10)) + p.DuoiPhieu+ '/0' + CAST (MONTH(p.NgayPhieu) as varchar(2)) + '-' + RIGHT(CAST(YEAR(p.NgayPhieu) as varchar(4)), 2)
			END as SoPhieu,
                 p.NgayPhieu,
                 dv.DonVi,
                 k.Kho,
                 p.TongGiaTri,
                 sp.SanPham,
                 l.LoaiSanPham
        from tblSanPham sp inner join tblDPNX p on sp.ID_SanPham = p.ID_SanPham
        inner join tblLoaiSanPham l on sp.ID_LoaiSanPham = l.ID_LoaiSanPham
        inner join tblKho k on p.ID_Kho = k.ID_Kho
        inner join tblDonVi dv on p.ID_DonVi = dv.ID_DonVi
        where p.NX ='X'
	)
GO
--/**************    fc_T_QuanLyHopDong    ***********
--	Ngày tạo: 18/3/2015
--	Người tạo: Hồng Thanh
--	Mục đích: Lấy danh sách các phiếu theo hợp đồng
--****************************************************/
CREATE function [dbo].[fc_T_QuanLyHopDong]()
returns table
as
	return (
		select distinct
			CASE 
				WHEN MONTH (p.NgayPhieu)> 9 THEN CAST(p.SoPhieu as varchar(10)) + p.DuoiPhieu + '/' + CAST (MONTH(p.NgayPhieu) as varchar(2)) + '-' + RIGHT(CAST(YEAR(p.NgayPhieu) as varchar(4)), 2)
                ELSE CAST(p.SoPhieu as varchar(10)) + p.DuoiPhieu+ '/0' + CAST (MONTH(p.NgayPhieu) as varchar(2)) + '-' + RIGHT(CAST(YEAR(p.NgayPhieu) as varchar(4)), 2)
			END as SoPhieu,
                 p.NgayPhieu,
                 dv.DonVi,
                 k.Kho,
                 p.TongGiaTri,
                 hd.SoHopDong
        from tblHopDong hd inner join tblTon t on hd.ID_HopDong = t.ID_HopDong
        inner join tblSLNX sl on t.ID_MatHang = sl.ID_MatHang
        inner join tblDPNX p on sl.ID_DPNX = p.ID_DPNX
        inner join tblKho k on p.ID_Kho = k.ID_Kho
        inner join tblDonVi dv on p.ID_DonVi = dv.ID_DonVi
        WHERE p.NX ='N'
	)
GO
--******************************************
--Noi dung: Lấy thông tin phiếu nhập xuất của mặt hàng
--Ngày tạo: 19/5/2014
--Ngày cập nhật: 18/3/2015
--Người tạo: Thanh
--******************************************/
ALTER proc [dbo].[sp_Thanh_TTNhapXuatMatHang]
@idMatHang int
as
	begin
		select p.NgayPhieu, 
		CASE 
				WHEN MONTH (p.NgayPhieu)> 9 THEN CAST(p.SoPhieu as varchar(10)) + p.DuoiPhieu + '/' + CAST (MONTH(p.NgayPhieu) as varchar(2)) + '-' + RIGHT(CAST(YEAR(p.NgayPhieu) as varchar(4)), 2)
                ELSE CAST(p.SoPhieu as varchar(10)) + p.DuoiPhieu+ '/0' + CAST (MONTH(p.NgayPhieu) as varchar(2)) + '-' + RIGHT(CAST(YEAR(p.NgayPhieu) as varchar(4)), 2)
		END as PhieuNhap,
		'' as PhieuXuat,ct.SLTH as SLNhap, 0 as SLXuat, ct.GhiChu
		from tblSLNX ct, tblDPNX p
		where ct.ID_DPNX= p.ID_DPNX and ct.ID_MatHang = @idMatHang
		and p.NX='N'
		union all
		select p.NgayPhieu,'' as PhieuNhap ,
		CASE 
				WHEN MONTH (p.NgayPhieu)> 9 THEN CAST(p.SoPhieu as varchar(10)) + p.DuoiPhieu + '/' + CAST (MONTH(p.NgayPhieu) as varchar(2)) + '-' + RIGHT(CAST(YEAR(p.NgayPhieu) as varchar(4)), 2)
                ELSE CAST(p.SoPhieu as varchar(10)) + p.DuoiPhieu+ '/0' + CAST (MONTH(p.NgayPhieu) as varchar(2)) + '-' + RIGHT(CAST(YEAR(p.NgayPhieu) as varchar(4)), 2)
		END as PhieuXuat,
		0 as SLNhap, ct.SLTH as SLXuat,ct.GhiChu
		from tblSLNX ct, tblDPNX p
		where ct.ID_DPNX= p.ID_DPNX and ct.ID_MatHang = @idMatHang
		and p.NX='X'
	end